﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Pay.Model.Interfaces;
using System.Data;
namespace Pay.Dapper
{
   public static class DapperExtensions
    {

        /// <summary>
        /// 分页查询
        /// 此扩展会获取分页集合,同时还会获取分页前的总数
        /// </summary>
        /// <typeparam name="T">数据模型</typeparam>
        /// <param name="conn">IDBConnection</param>
        /// <param name="sql">sql</param>
        /// <param name="param">查询参数</param>
        /// <param name="offset">偏移量</param>
        /// <param name="rows">获取行数</param>
        /// <param name="orderByField">排序字段</param>
        /// <param name="asc">默认正序</param>
        /// <returns></returns>
        public async static Task<IPagedOutput<T>> PagedByAsync<T>(this IDbConnection conn, StringBuilder sql, DynamicParameters param, int offset, int rows, string orderByField = "Id", bool asc = true)
        {
            var pagedOutput = new PagedOutput<T>();
            var items = (await GetPagedListAsync<T>(conn, sql, param, offset, rows, orderByField, asc)).ToList();
            pagedOutput.TotalCount = await GetCountAsync(conn, sql, param);
            pagedOutput.Items = items.ToList();
            return pagedOutput;
        }

        /// <summary>
        /// 分页查询
        /// 此扩展会获取分页集合
        /// </summary>
        /// <typeparam name="T">数据模型</typeparam>
        /// <param name="conn">IDBConnection</param>
        /// <param name="sql">sql</param>
        /// <param name="param">查询参数</param>
        /// <param name="offset">偏移量</param>
        /// <param name="rows">获取行数</param>
        /// <param name="orderByField">排序字段</param>
        /// <param name="asc">默认正序</param>
        /// <returns></returns>
        public async static Task<IPagedListOutput<T>> PagedListByAsync<T>(this IDbConnection conn, StringBuilder sql, DynamicParameters param, int offset, int rows, string orderByField = "Id", bool asc = false)
        {
            var pagedOutput = new PagedListOutput<T>();
            pagedOutput.Items = (await GetPagedListAsync<T>(conn, sql, param, offset, rows, orderByField, asc)).ToList();
            return pagedOutput;
        }

        private async static Task<IEnumerable<T>> GetPagedListAsync<T>(IDbConnection conn, StringBuilder sql, DynamicParameters param, int offset, int rows, string orderByField, bool asc)
        {
            //sql.Append(" ORDER BY Id DESC OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY ");
            //param.Add("@offset", offset, System.Data.DbType.Int32);
            //param.Add("@rows", rows, System.Data.DbType.Int32);

            // 避免破坏传入的sql和参数
            var pageParam = new DynamicParameters(param);
            //var pageQuery = string.Format(@"select * from (
            //                 select row_number() over(order by {0}) as rowNum, * from ({1}) t 
            //                ) t where rowNum between @offset and @rows", orderByField, sql);
            var pageQuery = new StringBuilder();
            pageQuery.Append(sql).Append(" ORDER BY ").Append(orderByField).Append(asc == true ? " ASC " : " DESC ").Append(" OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY ");
            pageParam.Add("@offset", offset, System.Data.DbType.Int32);
            pageParam.Add("@rows", rows, System.Data.DbType.Int32);

            return await conn.QueryAsync<T>(pageQuery.ToString(), pageParam);
        }

        private async static Task<int> GetCountAsync(IDbConnection conn, StringBuilder sql, DynamicParameters param)
        {
            param = param ?? new DynamicParameters();
            // 在分页前查询结果
            var totalCount = await conn.QueryFirstAsync<int>($"SELECT COUNT(0) Counts FROM ({sql}) t", param) ;
            return totalCount;
        }
    }
}
